#!/var/bin/env python
# -*- coding: utf-8 -*-

import time
import json
import datetime
import os
import logging as log
import smtplib
import glob
import psycopg2.extras
import pytz


# ログレベルを設定
log.basicConfig(level=log.DEBUG)


# 日付フォーマットチェック
def is_valid_date(str, format):
    try:
        time.strptime(str, format)
        return True
    except BaseException as bep:
        return False


# jsonファイルを読み込み
def read_json(json_file_path, csv_table_name=None):
    if not os.path.exists(json_file_path):
        raise OSError("JSONFileNotFoundError")
    with open(json_file_path) as json_fp:
        json_data = json.loads(json_fp.read())
        if csv_table_name is None:
            return json_data
        else:
            if csv_table_name not in json_data.keys():
                raise OSError("JSONKeyNotFoundError")
            return json_data[csv_table_name]


# pkによって、pk_placeholderを生成
def get_pk_placeholder(json_data):
    pk = json_data["pk"]
    pk_placeholder = "%s = %%s AND " * len(pk)
    pk_placeholder = pk_placeholder[:-4]
    return pk_placeholder


# UTC時間を取ります
def get_utctime(datatime, format):
    log.debug(datetime.datetime.strptime(datatime, format))
    csv_time = datetime.datetime.strptime(datatime, format).replace(tzinfo=pytz.timezone('Asia/Tokyo'))
    utc_time = csv_time.astimezone(pytz.utc)
    return utc_time


# CSVファイルを存在するかどうかチエック
def check_folder_is_existed(parameters):
    parameter_count = len(parameters)
    files = None
    if parameter_count == 2:
        log.debug(datetime.datetime.utcnow())
        timenow = (datetime.datetime.utcnow() + datetime.timedelta(hours=9))
        jp_time = timenow.strftime('%Y%m%d')
        log.debug(jp_time)
        csv_folder_name = jp_time
        if not os.path.exists(parameters[0] + csv_folder_name):
            raise OSError(parameters[0] + csv_folder_name + " FolderNotFoundError")
        log.debug('%s%s/%s' % (parameters[0], csv_folder_name, parameters[1]))
        files = glob.glob('%s%s/%s' % (parameters[0],csv_folder_name, parameters[1]))
        if len(files) == 0:
            raise OSError('%s%s/%s' % (parameters[0], csv_folder_name, parameters[1]) + " FileNotFoundError")
    elif parameter_count == 3:
        csv_folder_name = parameters[2]
        if not os.path.exists(parameters[0] + csv_folder_name):
            raise OSError(parameters[0] + csv_folder_name + " FolderNotFoundError")
        log.debug('%s%s/%s' % (parameters[0], csv_folder_name, parameters[1]))
        files = glob.glob('%s%s/%s' % (parameters[0], csv_folder_name, parameters[1]))
        if len(files) == 0:
            raise OSError('%s%s/%s' % (parameters[0], csv_folder_name, parameters[1]) + " FileNotFoundError")
    csv_file_name = files[0]
    return csv_file_name


# 実テーブルにデータを読み込み
def insert_data_to_maindb(json_data, conn, db_table_name, temp_data, tp_db_table_name):

    # key，valueによって、データを取得する
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    #処理タイプを取得
    key_type = temp_data["syori_type"]

    # ワークテーブルデータ主キーを取得
    data_id = temp_data["syori_id"]

    # 古いPKを削除
    if json_data["pkChangeFlg"] == "1":
        temp_data.pop("oya_cd")

    if json_data["pkChangeFlg"] == "2":
        temp_data.pop("wkfcode")

    # 更新者と更新日付をチェック
    update_user = temp_data["update_user"]
    update_datetime = temp_data["update_datetime"]

    if update_datetime is None:
        temp_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
        date_time = datetime.datetime.strptime(temp_time, '%Y-%m-%d %H:%M:%S')
        ts = int(time.mktime(date_time.timetuple()))
        date_timeWith_zone = datetime.datetime.fromtimestamp(ts, pytz.timezone('UTC'))
        update_datetime = date_timeWith_zone

    if update_user is None:
        update_user = json_data["batchId"]

    # 実テーブルのみ持ってるデータ
    temp_data["regist_user"] = update_user
    temp_data["regist_datetime"] = update_datetime
    temp_data["update_user"] = update_user
    temp_data["update_datetime"] = update_datetime

    # ワークテーブルのみ持ってるデータを削除
    temp_data.pop("syori_id")
    temp_data.pop("syori_type")
    temp_data.pop("csv_data")
    temp_data.pop("syori_flg")

    # placeholderを処理する
    pk_key_placeholder = get_pk_placeholder(json_data)
    pk_key_values = []
    pk_key = []
    for key in json_data["pk"]:
        key = key.lower()
        pk_key.append(key)
        pk_key_values.append(temp_data[key])

    if key_type == "D" or key_type == "d":
        temp_data["del_flg"] = "1"
        temp_data["del_datetime"] = update_datetime

        # SQL文を生成
        update_placeholder = " %s = %%s," * len(temp_data.keys())
        update_placeholder = update_placeholder[:-1]
        update_keys = [db_table_name] + temp_data.keys()
        update_values = temp_data.values()
        update_sql = "UPDATE %s SET " + update_placeholder + " WHERE " + pk_key_placeholder
        log.debug(update_sql % tuple(update_keys + pk_key))
        cur.execute(update_sql % tuple(update_keys + pk_key), update_values + pk_key_values)
    else:
        select_sql = "SELECT update_datetime FROM %s WHERE " + pk_key_placeholder
        log.debug(select_sql % tuple(pk_key + [db_table_name]))
        cur.execute(select_sql % tuple([db_table_name] + pk_key), pk_key_values)
        temp_data["del_flg"] = "0"
        if cur.rowcount != 0:
            # 更新を行ないます
            row_data = cur.fetchone()
            update_placeholder = "%s = %%s ," * len(temp_data.keys())
            update_placeholder = update_placeholder[:-1]
            update_keys = [db_table_name] + temp_data.keys()
            update_values = temp_data.values()
            update_sql = "UPDATE %s SET " + update_placeholder + "WHERE " + pk_key_placeholder
            log.debug(update_sql % tuple(update_keys + pk_key))
            # updateDateFlg
            if json_data['updateDateFlg'] == '1':
                cur.execute(update_sql % tuple(update_keys + pk_key), update_values + pk_key_values)
            else:
                if update_datetime > row_data["update_datetime"]:
                    cur.execute(update_sql % tuple(update_keys + pk_key), update_values + pk_key_values)
        else:
            # 追加します
            insert_key_placeholder_values = [db_table_name] + temp_data.keys()
            insert_value = temp_data.values()
            insert_key = len(temp_data.keys()) * ",%s"
            insert_key = insert_key[1:]
            temp_value = len(temp_data.values()) * ",%%s"
            temp_value = temp_value[1:]
            insert_sql = "INSERT INTO %s " + " (" + insert_key + ")" + " VALUES " + "(" + temp_value + ")"
            log.debug(insert_sql % tuple(insert_key_placeholder_values))
            cur.execute(insert_sql % tuple(insert_key_placeholder_values), insert_value)

    # ワークテーブルの処理フラグを更新する
    update_sql = """ UPDATE %s SET syori_flg = 1 WHERE syori_id = %%s """
    cur.execute(update_sql % tp_db_table_name, [data_id])
    cur.close()


# メール送信
def send_mail(to_mail_address, mail_body, accessory=True):
    smtp_server = "smtp.163.com"
    from_mail = 'fgb031102@163.com'
    mail_pass = 'fgb1984'
    to_mail = to_mail_address
    cc_mail = ['fon@linkjapan.co.jp']
    from_name = 'sky2016'
    subject = u'実行通知'.encode('utf-8')
    mail = [
        "From: %s <%s>" % (from_name, from_mail),
        "To: %s" % ','.join(to_mail),
        "Subject: %s" % subject,
        "Cc: %s" % ','.join(cc_mail),
        "",
        mail_body
    ]
    msg = '\n'.join(mail)
    if accessory:
        pass
    else:
        print (msg)
        s = smtplib.SMTP_SSL(smtp_server, 465)
        s.login(from_mail, mail_pass)
        s.sendmail(from_mail, to_mail + cc_mail, msg)
        s.quit()
